﻿namespace _3.Database_Search_Queries
{
	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Threading.Tasks;

	using _1.Maping_all_tables_in_DB_First;

	class Main_Task3
	{
		static void Main()
		{
			using (var databaseContext = new SoftUniEntities())
			{
				/*
				 * 1. Find all employees who have projects started in the time period 2001 - 2003 (inclusive).
				 * Select each employee's first name, last name and manager name and each of
				 * their projects' name, start date, end date.
				 */

				var firstEmployeeSet = databaseContext.Projects
					.Where(project => project.StartDate.Year >= 2001 && project.StartDate.Year <= 2003)
					.Select(project => new
					{
						Name = project.Name,
						StartDate = project.StartDate,
						EndDate = project.EndDate,
						Employees = project.Employees
							.Select(e => new
							{
								FirstName = e.FirstName,
								LastName = e.LastName,
								Manager = (from emp in databaseContext.Employees
										   where emp.ManagerID == e.ManagerID
										   select emp.FirstName + " " + emp.LastName)
							})
					})
					.ToList();

				//foreach (var item in firstEmployeeSet)
				//{
				//	Console.WriteLine(item.Name);
				//	Console.WriteLine(item.StartDate);
				//	Console.WriteLine(item.EndDate);

				//	Console.WriteLine(new string('-', 16));

				//	foreach (var employee in item.Employees)
				//	{
				//		Console.WriteLine(employee.FirstName);
				//		Console.WriteLine(employee.LastName);

				//		Console.WriteLine(new string('-', 16));

				//		foreach (var item2 in employee.Manager)
				//		{
				//			Console.WriteLine(item2);

				//			Console.WriteLine(new string('=', 16));
				//		}
				//	}
				//}

				// 2. Find all addresses, ordered by the number of employees who live there (descending),
				// then by town name (ascending). Take only the first 10 addresses and select their
				// address text, town name and employee count.

				var mostPopulatedAddresses = databaseContext.Addresses
					.OrderByDescending(a => a.Employees.Count)
					.ThenBy(a => a.Town.Name)
					.Take(10)
					.Select(a => new
					{
						Text = a.AddressText,
						Town = a.Town.Name,
						Count = a.Employees.Count
					})
					.ToList();

				mostPopulatedAddresses.ForEach(Console.WriteLine);

				// 3. Get an employee by id (e.g. 147). Select only his/her first name, last name, job title and
				// projects (only their names). The projects should be ordered by name (ascending).

				const int employeeID = 137;

				var employeeByID = databaseContext.Employees
					.Where(e => e.EmployeeID == employeeID)
					.Select(e => new
					{
						e.FirstName,
						e.LastName,
						e.JobTitle,
						Projects = e.Projects
							.Select(p => p.Name)
							.OrderBy(p => p)
					})
					.FirstOrDefault();

				Console.WriteLine(employeeByID.FirstName);
				Console.WriteLine(employeeByID.LastName);
				Console.WriteLine(employeeByID.JobTitle);

				foreach (var project in employeeByID.Projects)
				{
					Console.WriteLine(project);
				}

				/* 4. Find all departments with more than 5 employees. Order them by employee count (ascending).
				 * Select the department name, manager name and first name, last name, hire date and job title of every employee.
				 */

				var departmentsSet = databaseContext.Departments
					.Where(d => d.Employees.Count > 5)
					.OrderBy(d => d.Employees.Count)
					.Select(d => new
					{
						d.Name,
						ManagerName = databaseContext.Employees
							.Where(m => m.EmployeeID == d.ManagerID)
							.Select(m => m.FirstName + " " + m.LastName)
							.FirstOrDefault(),
						Employees = d.Employees
							.Select(e => new
							{
								e.FirstName,
								e.LastName,
								e.HireDate,
								e.JobTitle
							})
					});


			}
		}
	}
}